import sqlite3
import json
from utils.config_manager import config


def create_sh_table(db_path="robot_zl.db"):
    # 使用config获取数据库路径
    db_path =  config.get_db_path()
    """创建 sh 表"""
    with sqlite3.connect(db_path) as conn:
        cursor = conn.cursor()
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS sh (
                userId INTEGER PRIMARY KEY,
                datajson TEXT
            )
        ''')
        conn.commit()


def insert_or_update_data(data, user_id=1, db_path="robot_zl.db"):
    # 使用config获取数据库路径
    db_path =  config.get_db_path()
    """插入或更新 datajson 数据"""
    data_json = json.dumps(data)
    with sqlite3.connect(db_path) as conn:
        cursor = conn.cursor()
        cursor.execute('''
            INSERT OR REPLACE INTO sh (userId, datajson)
            VALUES (?, ?)
        ''', (user_id, data_json))
        conn.commit()


def get_json_value(json_path, user_id=1, db_path="robot_zl.db"):
    # 使用config获取数据库路径
    db_path =  config.get_db_path()
    """从 datajson 中提取指定路径的值"""
    if not json_path.startswith("$."):
        json_path = "$." + json_path

    with sqlite3.connect(db_path) as conn:
        cursor = conn.cursor()
        cursor.execute('''
            SELECT json_extract(datajson, ?) as value 
            FROM sh 
            WHERE userId = ?
        ''', (json_path, user_id))

        result = cursor.fetchone()
        return result[0] if result else None


def get_data_cs(json_path, ctNo, user_id=1, db_path="robot_zl.db"):
    # 使用config获取数据库路径
    db_path =  config.get_db_path()
    """从 datajson 中提取指定路径的值，并根据 ctNo 查找 address"""
    if not json_path.startswith("$."):
        json_path = "$." + json_path
    with sqlite3.connect(db_path) as conn:
        cursor = conn.cursor()
        cursor.execute('''
            SELECT json_extract(datajson, ?) as value 
            FROM sh 
            WHERE userId = ?
        ''', (json_path, user_id))

        result = cursor.fetchone()
        if not result or not result[0]:
            return None

        # 假设 result[0] 是一个列表，遍历查找 ctNo 对应的 address
        for item in eval(result[0]):
            if int(item.get("name")) == ctNo:
                return int(item.get("address"))

        return None


def get_data(user_id=1, db_path="robot_zl.db"):
    """从 datajson 中提取指定路径的值"""
    # 使用config获取数据库路径
    db_path =  config.get_db_path()
    with sqlite3.connect(db_path) as conn:
        cursor = conn.cursor()
        cursor.execute('''
            SELECT datajson 
            FROM sh 
            WHERE userId = ?
        ''', (user_id,))

        result = cursor.fetchone()
        return json.loads(result[0]) if result else None


def get_fl_data(user_id=1, db_path="robot_zl.db"):
    # 使用config获取数据库路径
    db_path =  config.get_db_path()
    with sqlite3.connect(db_path) as conn:
        cursor = conn.cursor()
        cursor.execute("SELECT datajson FROM fl WHERE userId = 1")
        result = cursor.fetchone()
        return json.loads(result[0]) if result else None


# 使用示例
if __name__ == "__main__":
    # 创建表
    create_sh_table()

    # 示例数据
    data = {
        "set": [
            {"name": "ct1", "address": "addr1"},
            {"name": "ct2", "address": "addr2"}
        ]
    }

    # 插入数据
    insert_or_update_data(data, user_id=1)

    # 获取 JSON 值
    value = get_json_value("set", user_id=1)
    print(f"set 的值: {value}")

    # 获取 ctNo 对应的 address
    address = get_data_cs("set", "ct2", user_id=1)
    print(f"ct2 的 address: {address}")